package book.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import book.dao.BaseDao;
import book.dao.Library_cardDao;
import book.util.Library_card;

public class Library_cardDaoImpl implements Library_cardDao{
		private Connection connection;
		private ResultSet rs;
		private PreparedStatement pstmt;
	public int insertinto(Library_card library_card) {
		int num=0;
		try {
		connection=BaseDao.getConnection();
		String sql="insert into library_card(card_ID,Username,Company,Type)values(?,?,?,?)";
		
			pstmt=connection.prepareStatement(sql);
			pstmt.setInt(1, library_card.getCard_ID());
			pstmt.setString(2, library_card.getUsername());
			pstmt.setString(3, library_card.getCompany());
			pstmt.setString(4, library_card.getType());
			num=pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			BaseDao.closeAll(rs, pstmt, connection);
		}
			return num;
	}

	public int deletefrom(int card_ID) {
			int num=0;
			try {
			connection=BaseDao.getConnection();
			String sql="delete from library_card where card_ID=?";
			
				pstmt.setInt(1, card_ID);
				pstmt=connection.prepareStatement(sql);
				num=pstmt.executeUpdate();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally{
				BaseDao.closeAll(rs, pstmt, connection);
			}
			return num;
	}

	public int updateset(Library_card library_card) {
		int num=0;
			try {
		connection=BaseDao.getConnection();
		
		String sql="update library_card set Username=?, Company=?, Type=? where card_ID=?";
	
			pstmt=connection.prepareStatement(sql);
			pstmt.setString(1, library_card.getUsername());
			pstmt.setString(2, library_card.getCompany());
			pstmt.setString(3, library_card.getType());
			pstmt.setInt(4, library_card.getCard_ID());
			num=pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			BaseDao.closeAll(rs, pstmt, connection);
		}
	
		return num;
	}

	public List<Library_card> findLibrary_cardAll() {
		List<Library_card>  lylist=new ArrayList<Library_card>();
		try {
		connection=BaseDao.getConnection();
		String sql="select card_ID,Username,Company,Type from library_card";
		
			pstmt=connection.prepareStatement(sql);
			rs=pstmt.executeQuery();
			while(rs.next()){
				Library_card a=new Library_card(rs.getInt("card_ID"),rs.getString("Username"),
						rs.getString("Company"),rs.getString("Type"));
				lylist.add(a);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			BaseDao.closeAll(rs, pstmt, connection);
		}
		return lylist;
	}

}
